from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, SmallInteger, DATETIME, Time, Date, create_engine, Float, func
from sqlalchemy import desc, and_, or_
from datetime import datetime, date, time, timedelta
import pandas as pd
import math

"""
    学生各科成绩排名
"""

DATABASE_URI = 'mysql://root:xxx@xxx:3306/xxx?charset=utf8mb4'

engine = create_engine(DATABASE_URI)

Base = declarative_base()

Session = sessionmaker(bind=engine)


class Cjpm(Base):
    __tablename__ = 'sjkf_t_student_xkcjpm'
    id = Column(String, primary_key=True)
    grade = Column(String)
    stud_class = Column(String)
    ranking = Column(String)
    student_code = Column(String)
    login_name = Column(String)
    student_name = Column(String)
    nature = Column(String)
    yearAndGrade = Column(DATETIME)
    per = Column(String)
    semester_id = Column(String)
    total_score = Column(String)
    subject_name = Column(String)

    def __repr__(self):
        return f"<Cjpm(grade={self.grade}" \
               f", ranking='{self.ranking}'" \
               f", student_code='{self.student_code}'" \
               f", student_name='{self.student_name}'" \
               f")>"


def deleteCjpm():
    session = Session()

    session.query(Cjpm).delete()

    session.commit()
    session.close()


def queryCjpm(**kwargs):
    session = Session()
    query = session.query(Cjpm)
    # 添加条件
    for key, value in kwargs.items():
        if hasattr(Cjpm, key):
            query = query.filter(getattr(Cjpm, key) == value)

    # 执行查询并返回结果
    cjpms = query.all()
    session.close()
    return cjpms


def addCjpm(datas):
    session = Session()
    number = 0
    onedata = {}
    all_data = pd.DataFrame()
    # firstDat = datas[0:1]
    try:
        all_data = datas
        for index, data in datas.iterrows():
            number = index
            onedata = data
            if data['grade'] is None:
                datas.at[index, 'grade'] = ''
            if data['stud_class'] is None:
                datas.at[index, 'stud_class'] = ''
            if data['student_code'] is None:
                datas.at[index, 'student_code'] = ''
            if data['login_name'] is None:
                datas.at[index, 'login_name'] = ''
            if data['student_name'] is None:
                datas.at[index, 'student_name'] = ''
            if data['nature'] is None:
                nature = ''
            else:
                nature = datas.at[index, 'nature']
            if data['yearAndGrade'] is None:
                datas.at[index, 'yearAndGrade'] = ''
            if data['per'] is None:
                datas.at[index, 'per'] = ''
            if data['ranking'] is None:
                ranking = 0
            else:
                ranking = datas.at[index, 'ranking']
            if data['subject_name'] is None:
                datas.at[index, 'subject_name'] = ''
            if data['semester_id'] is None:
                datas.at[index, 'semester_id'] = ''
            if data['total_score'] is None or math.isnan(data['total_score']):
                datas.at[index, 'total_score'] = 0.0
            cjpm = Cjpm(
                id=datas.at[index, 'login_name'] + datas.at[index, 'student_code'] + datas.at[index, 'subject_name'],
                grade=datas.at[index, 'grade'],
                stud_class=datas.at[index, 'stud_class'],
                ranking=int(ranking),
                student_code=datas.at[index, 'student_code'],
                login_name=datas.at[index, 'login_name'],
                student_name=datas.at[index, 'student_name'],
                nature=nature,
                yearAndGrade=datas.at[index, 'yearAndGrade'],
                per=datas.at[index, 'per'],
                total_score=float(datas.at[index, 'total_score']),
                subject_name=datas.at[index, 'subject_name'],
                semester_id=datas.at[index, 'semester_id']
            )

            session.add(cjpm)
            session.commit()
        session.close()
    except:
        print(f"Error: 插入失败{number}----{onedata}")
        print("------------------------")
        print(f"{all_data}")


class Score(Base):
    __tablename__ = 'jwxtnew_ach_overall_score'
    id = Column(String, primary_key=True)
    semester_id = Column(String)
    class_id = Column(String)
    class_name = Column(String)
    student_id = Column(String)
    student_name = Column(String)
    custom_overallterm = Column(Float)
    create_date = Column(DATETIME)
    subject_id = Column(String)
    subject_name = Column(String)

    def __repr__(self):
        return f"<Score(student_name={self.student_name}" \
               f", custom_overallterm='{self.custom_overallterm}')>"


class Semester(Base):
    __tablename__ = 'jwxtnew_base_semester_info'
    id = Column(String, primary_key=True)
    academic_year_id = Column(String)
    name = Column(String)
    start_date = Column(DATETIME)
    end_date = Column(DATETIME)
    create_time = Column(DATETIME)
    current_semester_flag = Column(Integer)

    def __repr__(self):
        return f"<Semester(name={self.name}" \
               f", start_date='{self.start_date}'" \
               f", end_date='{self.end_date}'" \
               f")>"


class AcademicYear(Base):
    __tablename__ = 'jwxtnew_base_academic_year'
    id = Column(String, primary_key=True)
    name = Column(String)
    start_date = Column(DATETIME)

    def __repr__(self):
        return f"<AcademicYear(id={self.id}" \
               f", name='{self.name}'" \
               f")>"


class StudClass(Base):
    __tablename__ = 'jwxtnew_stud_class'
    id = Column(String, primary_key=True)
    name = Column(String)
    academic_year_id = Column(DATETIME)
    grade_id = Column(DATETIME)
    grade_name = Column(DATETIME)

    def __repr__(self):
        return f"<StudClass(id={self.id}" \
               f", name='{self.name}'" \
               f", academic_year_id='{self.academic_year_id}'" \
               f", grade_name='{self.grade_name}'" \
               f")>"


def queryStudClass(id, academic_year_id, grade_name):
    session = Session()
    query = session.query(StudClass)

    if id:
        query = query.filter(
            StudClass.id == id
        )
    if academic_year_id:
        query = query.filter(
            StudClass.academic_year_id == academic_year_id
        )
    if grade_name:
        query = query.filter(
            StudClass.grade_name.like(grade_name)
        )
    # 添加条件
    # for key, value in kwargs.items():
    #     if hasattr(StudClass, key):
    #         query = query.filter(getattr(StudClass, key) == value)

    # 执行查询并返回结果
    studClasses = query.all()
    session.close()
    return studClasses


# 学生表
class Student(Base):
    __tablename__ = 'jwxtnew_v_nbxc_student'
    LOGIN_NAME = Column(String, primary_key=True)
    grade_name = Column(String)
    name = Column(String)
    student_name = Column(String)
    student_code = Column(String)
    student_id = Column(String)

    def __repr__(self):
        return f"<Student(LOGIN_NAME={self.LOGIN_NAME}" \
               f", student_name='{self.student_name}')>"


def queryAllStudent(**kwargs):
    session = Session()
    students = session.query(Student)

    for key, value in kwargs.items():
        if hasattr(Score, key):
            if isinstance(value, list):
                class_id_conditions = [Score.class_id == class_id for class_id in value]
                students = students.filter(or_(*class_id_conditions))
            else:
                students = students.filter(getattr(Score, key) == value)

    students = students.all()
    session.close()
    return students


def queryStudent(**kwargs):
    session = Session()
    students = session.query(Student)

    for key, value in kwargs.items():
        if hasattr(Student, key):
            students = students.filter(getattr(Student, key) == value)

    students = students.first()
    session.close()
    return students


# 学生信息
class StatusBasic(Base):
    __tablename__ = 'jwxtnew_stud_personal_status_basic'
    id = Column(String, primary_key=True)
    profile_id = Column(String)
    stud_nature = Column(String)

    def __repr__(self):
        return f"<StatusBasic(profile_id={self.profile_id}" \
               f", stud_nature='{self.stud_nature}')>"


def queryStatusBasic(**kwargs):
    session = Session()
    query = session.query(StatusBasic)
    # 添加条件
    for key, value in kwargs.items():
        if hasattr(StatusBasic, key):
            query = query.filter(getattr(StatusBasic, key) == value)

    # 执行查询并返回结果
    basics = query.first()
    session.close()
    return basics


def queryScore(**kwargs):
    session = Session()
    # 查询每个学生在特定科目上的总分数
    # subject_names = ['语文', '数学', '外语', '英语', '德语', '日语', '法语', '西班牙语', '物理', '化学', '生物', '思想政治', '历史', '地理']
    # conditions = [Score.subject_name == subject_name for subject_name in subject_names]

    total_scores_query = session.query(
        Score.semester_id,
        Score.student_id,
        Score.class_id,
        Score.custom_overallterm.label('total_score'),
        Score.subject_name
    )
    # .filter(or_(*conditions))
    # 添加条件
    for key, value in kwargs.items():
        if hasattr(Score, key):
            if key == 'class_id':
                class_id_conditions = [Score.class_id == class_id for class_id in value]
                total_scores_query = total_scores_query.filter(or_(*class_id_conditions))
            elif key == 'student_id':
                student_id_conditions = [Score.student_id == student_id for student_id in value]
                total_scores_query = total_scores_query.filter(or_(*student_id_conditions))
            elif key == 'subject_name' and isinstance(value, list):
                subject_name_conditions = [Score.subject_name == subject_name for subject_name in value]
                total_scores_query = total_scores_query.filter(or_(*subject_name_conditions))
            else:
                total_scores_query = total_scores_query.filter(getattr(Score, key) == value)

    total_scores = total_scores_query.all()

    # 查询每个学生的基本信息
    # students_info_query = session.query(
    #     Score.id,
    #     Score.semester_id,
    #     Score.class_name,
    #     Score.student_id,
    #     Score.student_name,
    #     Score.custom_overallterm,
    #     Score.create_date,
    #     Score.subject_id,
    #     Score.subject_name
    # )
    #
    # # 添加相同的条件
    # for key, value in kwargs.items():
    #     if hasattr(Score, key):
    #         if key == 'class_id':
    #             class_id_conditions = [Score.class_id == class_id for class_id in value]
    #             total_scores_query = total_scores_query.filter(or_(*class_id_conditions))
    #         elif key == 'student_id':
    #             student_id_conditions = [Score.student_id == student_id for student_id in value]
    #             total_scores_query = total_scores_query.filter(or_(*student_id_conditions))
    #         else:
    #             total_scores_query = total_scores_query.filter(getattr(Score, key) == value)
    #
    # students_info = students_info_query.all()
    students_info = ""
    # 结束会话
    session.close()

    # 返回结果
    return (students_info, total_scores)


def querySemester(**kwargs):
    session = Session()
    query = session.query(Semester)
    # 添加条件
    for key, value in kwargs.items():
        if hasattr(Semester, key):
            query = query.filter(getattr(Semester, key) == value)

    # 执行查询并返回结果
    academic_years = query.order_by(desc(Semester.start_date)).all()
    session.close()
    return academic_years


def queryAcademicYear(**kwargs):
    session = Session()
    query = session.query(AcademicYear)
    # 添加条件
    for key, value in kwargs.items():
        if hasattr(AcademicYear, key):
            query = query.filter(getattr(AcademicYear, key) == value)

    # 执行查询并返回结果
    academic_years = query.order_by(desc(AcademicYear.start_date)).first()
    session.close()
    return academic_years


"""
数据插入逻辑：
# 排名表里面有没有数据
    # 有数据，是不是新的数据
        # 是新数据，跳过
        # 不是新数据
            #     总评表当前学期有没有数据
                    #   没有新数据，跳过
                    #   有新数据，全删全插
    # 没数据
    #     当前学期有没有数据
            # 有数据直接插入新数据
            # 查上一学期，全插

"""


def before():
    cjpms = queryCjpm()
    cur_semester = querySemester(current_semester_flag=1)
    if len(cur_semester) > 0:
        cur_semester_id = cur_semester[0].id
    newest_cjpms = queryCjpm(semester_id=cur_semester_id)
    _, scores = queryScore(semester_id=cur_semester_id)
    deleteCjpm()
    if cjpms:
        if not newest_cjpms:
            if scores and scores > 0:
                handleData(True)
            else:
                handleData(False)
    else:
        if scores and scores > 0:
            handleData(True)
        else:
            handleData(False)


def handleData(is_cur_semester):
    if is_cur_semester:
        semesters = querySemester(current_semester_flag=1)
        if len(semesters) > 0:
            cur_semester = semesters[0]
    else:
        semesters = querySemester()
        if len(semesters) > 1:
            cur_semester = semesters[1]
    cur_semester_id = cur_semester.id
    cur_academic_year_id = cur_semester.academic_year_id
    academic_year = queryAcademicYear(id=cur_academic_year_id)
    academic_year_id = academic_year.id
    academic_year_name = academic_year.name
    print("------------------学年：" + academic_year_name + "--------------------")
    cur_year_name = cur_semester.name
    print("------------------学期：" + cur_year_name + "--------------------")
    # 获得学年和年级，以及对应的班级
    student_Classes = queryStudClass(id='', academic_year_id=academic_year_id, grade_name='%高%')
    grade_id_list = pd.DataFrame(columns=['grade_name', 'classId', 'class_name'])
    for student_Class in student_Classes:
        grade_name = student_Class.grade_name
        id = student_Class.id
        name = student_Class.name
        grade_id_list.loc[len(grade_id_list)] = [grade_name, id, name]

    all_grade = set(grade_id_list['grade_name'])
    all_student = queryAllStudent()
    all_student_list = []
    for student in all_student:
        student_id = student.student_id
        all_student_list.append(student_id)
    pre_pd = pd.DataFrame()
    subject_names = ['语文', '数学', ['英语', '德语', '日语', '法语', '西班牙语'], '物理', '化学', '生物', '思想政治', '历史', '地理']
    # subject_names = ['数学', ['英语', '德语', '日语', '法语', '西班牙语']]
    for subject in subject_names:
        for cur_grade in all_grade:
            print("------学期：" + cur_semester_id + "------")
            print("------年级：" + cur_grade + "------")
            print("------学科：" + str(subject) + "------")
            all_classes = grade_id_list.loc[grade_id_list['grade_name'] == cur_grade]['classId']
            all_classes_name = grade_id_list.loc[grade_id_list['grade_name'] == cur_grade]['class_name']
            print("------班级：------")
            print(all_classes.tolist())
            print(all_classes_name.tolist())
            _, total_scores = queryScore(semester_id=cur_semester_id
                                         , class_id=all_classes.tolist()
                                         , student_id=all_student_list
                                         , subject_name=subject)
            print("------分数num：" + str(len(total_scores)) + "------")
            if len(total_scores) == 0:
                continue
            total_scores_pd = pd.DataFrame(total_scores,
                                           columns=['semester_id', 'student_id', 'class_id', 'total_score',
                                                    'subject_name']) \
                .sort_values(by='total_score', ascending=False).reset_index(drop=True)

            total_scores_pd['semester_id'] = cur_semester_id
            total_scores_pd['grade'] = cur_grade
            total_scores_pd['stud_class'] = ''
            total_scores_pd['new_index'] = range(1, len(total_scores_pd) + 1)
            total_scores_pd['ranking'] = range(1, len(total_scores_pd) + 1)
            total_scores_pd['student_code'] = ''
            total_scores_pd['login_name'] = ''
            total_scores_pd['student_name'] = ''
            total_scores_pd['nature'] = ''
            total_scores_pd['yearAndGrade'] = ''
            total_scores_pd['per'] = ''
            for index, cur_total_score in total_scores_pd.iterrows():
                if isinstance(total_scores_pd.at[index, 'subject_name'], list):
                    total_scores_pd.at[index, 'subject_name'] = '外语'
                cur_student = queryStudent(student_id=cur_total_score.student_id)
                student_class = queryStudClass(id=cur_total_score.class_id, academic_year_id='',
                                               grade_name='')
                basic = queryStatusBasic(profile_id=cur_total_score.student_id)
                if student_class and len(student_class) > 0:
                    total_scores_pd.at[index, 'stud_class'] = student_class[0].name
                if cur_student:
                    total_scores_pd.at[index, 'student_code'] = cur_student.student_code
                    total_scores_pd.at[index, 'login_name'] = cur_student.LOGIN_NAME
                    total_scores_pd.at[index, 'student_name'] = cur_student.student_name
                    cur_student_grade = cur_student.name
                if basic:
                    total_scores_pd.at[index, 'nature'] = basic.stud_nature
                total_scores_pd.at[index, 'yearAndGrade'] = academic_year_name + ' ' + cur_student_grade

                semester_id = cur_total_score['semester_id']
                student_id = cur_total_score['student_id']
                cur_total_score = total_scores_pd.loc[
                    (total_scores_pd['semester_id'] == semester_id) &
                    (total_scores_pd['student_id'] == student_id)
                    ]

                pre_score = total_scores_pd.iloc[index - 1]
                pre_semester_id = pre_score['semester_id']
                pre_student_id = pre_score['student_id']
                pre_total_score = total_scores_pd.loc[
                    (total_scores_pd['semester_id'] == pre_semester_id) &
                    (total_scores_pd['student_id'] == pre_student_id)
                    ]
                if index == 0:
                    total_scores_pd.at[index, 'ranking'] = total_scores_pd.iloc[index]['ranking']
                else:
                    if cur_total_score['total_score'].item() == pre_total_score['total_score'].item():
                        total_scores_pd.at[index, 'ranking'] = total_scores_pd.iloc[index - 1]['ranking']
                    elif cur_total_score['total_score'].item() != pre_total_score['total_score'].item():
                        total_scores_pd.at[index, 'ranking'] = cur_total_score['new_index']
                total_scores_pd.at[index, 'per'] = str(
                    round(total_scores_pd.at[index, 'ranking'] / len(total_scores_pd) * 100, 2)) + "%"
            if len(total_scores_pd) > 0:
                print(f"------------插入数据{len(total_scores_pd)}条----------------")
                addCjpm(total_scores_pd)
                print("<br>")
                pre_pd = pre_pd.append(total_scores_pd)
    other_pd = pd.DataFrame()
    for student in all_student:
        student_id = student.student_id
        student_code = student.student_code
        student_name = student.student_name
        login_name = student.LOGIN_NAME
        grade_name = student.grade_name
        name = student.name
        basic = queryStatusBasic(profile_id=student_id)
        if not pre_pd.empty:
            cur_score = pre_pd['login_name'].isin([login_name]).any()
            if not cur_score:
                for subject in subject_names:
                    new_row = {}
                    if isinstance(subject, list):
                        new_row['subject_name'] = '外语'
                    else:
                        new_row['subject_name'] = subject
                    new_row['semester_id'] = cur_semester_id
                    new_row['grade'] = grade_name
                    new_row['stud_class'] = name
                    new_row['ranking'] = 0
                    new_row['student_code'] = student_code
                    new_row['login_name'] = login_name
                    new_row['student_name'] = student_name
                    if basic:
                        if basic.stud_nature is None:
                            new_row['nature'] = ''
                        else:
                            new_row['nature'] = basic.stud_nature
                    else:
                        new_row['nature'] = ''
                    new_row['yearAndGrade'] = academic_year_name + ' ' + grade_name
                    new_row['per'] = ''
                    new_row['total_score'] = 0
                    other_pd = other_pd.append(pd.DataFrame([new_row]), ignore_index=True)
    if len(other_pd) > 0:
        print(f"------------插入数据{len(other_pd)}条----------------")
        addCjpm(other_pd)
        print("<br> 2")

    print("------遍历结束------")


if __name__ == '__main__':
    before()
